﻿using Luna小店.DTO;
using Luna小店.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Luna小店.service
{
    class 面料导入Service
    {
        public static List<面料导入DTO> CSVToList(String path)
        {
            List<面料导入DTO> Dto = File.ReadAllLines(path,Encoding.ASCII).Skip(1).Select(v => FromCSV(v)).ToList();
            return Dto;
        }
        public static 面料导入DTO FromCSV(String csvLine)
        {
            String[] values = csvLine.Split(',');
            面料导入DTO dto = new 面料导入DTO(values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], values[11], values[12], values[14], values[17], values[18], values[19], values[20]);
            return dto;
        }
        public static List<面料导入DTO> GetDTOsFromExcelByConn(bool hasTitle = false)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            //openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
            openFile.Filter = MIMEConst.EXCEL_MIME;
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            openFile.Multiselect = false;
            if (openFile.ShowDialog() == DialogResult.Cancel) return null;
            var filePath = openFile.FileName;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType)) return null;

            using (DataSet ds = new DataSet())
            {
                string strCon = string.Format("Provider=Microsoft.Jet.OLEDB.{0}.0;" +
                                "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
                                "data source={3};",
                                (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
                string strCom = " SELECT * FROM [Fabric$]";
                using (OleDbConnection myConn = new OleDbConnection(strCon))
                using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
                {
                    myConn.Open();
                    myCommand.Fill(ds);
                }
                if (ds == null || ds.Tables.Count <= 0) return null;
                DataTable dt = ds.Tables[0];
                List<面料导入DTO> dtos = new List<面料导入DTO>();
                foreach(DataRow dr in dt.Rows)
                {
                    if (dr["MATERIAL_CODE"].ToString() != String.Empty && dr["MATERIAL_CODE"].ToString() != "面料号")
                    {
                        dtos.Add(new 面料导入DTO(dr));
                    }
                }
                return dtos;
            }
        }
    }
}
